﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class EmployeeStatusDAL : IEmployeeStatusDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public EmployeeStatusDAL()
        {

        }

        public IList<EmployeeStatusInfo> LoadListEmployeeStatus(EmployeeStatusInfo stInfo)
        {
            string strSQL;

            List<EmployeeStatusInfo> infoList = new List<EmployeeStatusInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", stInfo.Sequence);

                strSQL = "SELECT EMP_STATUS_SEQ, EMP_STATUS_NAME, ACTIVE"
                     + " FROM HRM_PS_EMPLOYEE_STATUSES"
                     + " WHERE RECORD_STATUS not like @DeleteRecord"
                     + (stInfo.Sequence > 0 ? " AND EMP_STATUS_SEQ = @Sequence" : "");
                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeStatusInfo Info = new EmployeeStatusInfo(rdr.GetInt32(0)
                        , rdr.IsDBNull(1) ? "" : rdr.GetString(1)
                        , rdr.IsDBNull(2) ? false : Convert.ToBoolean(Convert.ToInt32(rdr.GetString(2))));
                    infoList.Add(Info);
                }
                // dbManager.Dispose();
                return infoList;
            }
            catch (Exception ex)
            {
                //dbManager.Dispose();
                //return null;
                throw new Exception("class EmployeeStatusDAL:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public IList<EmployeeStatusInfo> LoadListEmployeeStatusGroupByActive()
        {
            string strSQL = "";

            List<EmployeeStatusInfo> infoList = new List<EmployeeStatusInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL += @"SELECT -1 as EMP_STATUS_SEQ, '--แสดงทั้งหมด--' as EMP_STATUS_NAME, 0 as ACTIVE" + Environment.NewLine;
                strSQL += @"UNION SELECT -2 as EMP_STATUS_SEQ, '--แสดงที่ปฎิบัติงาน--' as EMP_STATUS_NAME, 0 as ACTIVE" + Environment.NewLine;
                strSQL += @"UNION SELECT EMP_STATUS_SEQ, EMP_STATUS_NAME, ACTIVE
                          FROM HRM_PS_EMPLOYEE_STATUSES
                          WHERE RECORD_STATUS not like @DeleteRecord and ACTIVE = 1" + Environment.NewLine;
                strSQL += @"UNION SELECT -3 as EMP_STATUS_SEQ, '--แสดงที่ไม่ได้ปฎิบัติงาน--' as EMP_STATUS_NAME, 0 as ACTIVE" + Environment.NewLine;
                strSQL += @"UNION SELECT EMP_STATUS_SEQ, EMP_STATUS_NAME, ACTIVE
                          FROM HRM_PS_EMPLOYEE_STATUSES
                          WHERE RECORD_STATUS not like @DeleteRecord and ACTIVE = 0";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeStatusInfo Info = new EmployeeStatusInfo(rdr.GetInt32(0)
                        , rdr.IsDBNull(1) ? "" : rdr.GetString(1)
                        , !rdr.IsDBNull(2) && rdr.GetInt32(2) == 1);
                    infoList.Add(Info);
                }
                // dbManager.Dispose();
                return infoList;
            }
            catch (Exception ex)
            {
                //dbManager.Dispose();
                //return null;
                throw new Exception("class EmployeeStatusDAL:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }  
        
    }
}
